<?php  
/**
* 
*/
class BorrowingSystem_Model extends CI_Model
{
	
	function __construct()
	{
		parent::__construct();

	}

	public function getListCaseFiles(){
		return $this->db->query("SELECT cf.file_id, cf.file_caseid,cf.file_casename,cd.doc_amt,cf.file_casedetail,cf.file_status,cf.file_brstatus
			FROM Case_file AS cf
			   Left JOIN (SELECT file_id,count(document_id) AS doc_amt  
			   	FROM Case_document WHERE document_status = 'Y' OR document_status is NULL GROUP BY file_id) AS cd ON cf.file_id = cd.file_id
			ORDER BY cf.file_caseid ASC
")->result_array();
	}

	public function getCaseFileWithCaseID($id){
		$sql = "SELECT cf.file_id,cf.file_casename,cf.file_caseid,cd.document_id,cd.document_name,cd.document_group,cd.index_code ,cd.document_detail	
			FROM case_file AS cf LEFT JOIN case_document AS cd ON cf.file_id = cd.file_id
			WHERE cd.file_id = '".$id."'";
		return $this->db->query($sql)->result_array();
	}

	public function getListGroup(){
		return $this->db->query("SELECT ref_id, ref_name FROM Mst_reference WHERE ref_group = 'borrow_doctype' AND ref_status = 'A' ORDER BY ref_name")->result_array();
	}

	public function checkIdFileCase($id){
		return $this->db->query('SELECT index_code FROM case_document WHERE index_code ='.$id)->num_rows();
	}

	public function add_filecase($new){
		return $this->db->insert('case_document',$new);
	}

	public function del_filecase($id){
		return $this->db->query('DELETE case_document WHERE document_id = '.$id);
	}

	public function checkIdCase($id){
		return $this->db->query('SELECT file_caseid FROM case_file WHERE file_caseid = '.$id)->num_rows();
	}

	public function createCase($new){
		return $this->db->insert('Case_file',$new);
	}

	public function change_status($id,$data){

		$this->db->where('file_id',$id);
		return $this->db->update('Case_file',$data);
	}

	public function getCaseFile($id){
		return $this->db->where('file_id',$id)->get('Case_file')->row_array();
	}

	public function insert($newCaseFile){
		$this->db->insert('Case_file', $newCaseFile); 
	}

	public function update($id,$file){
		$this->db->where('file_id',$id)->update('Case_file',$file);
	}

	public function getLastFileID(){
		$rs = $this->db->query('SELECT TOP 1 file_id FROM Case_file ORDER BY file_id DESC')->row_array();
		return $rs['file_id'];
	}

	// Find Case System
	public function findCase($condition){
		$sql = "SELECT Case_file.file_caseid,Case_file.file_casename,Case_file.file_casedetail,Case_file.file_status,		
	SUM(CONVERT(int,case_file.file_brstatus)) AS sumOfBorrowed, COUNT(case_file.file_brstatus) AS totalCaseFile
FROM Case_file		
WHERE 1=1 ".$condition."GROUP BY Case_file.file_caseid,Case_file.file_casename,Case_file.file_casedetail,Case_file.file_status	";

		// $sql = "SELECT file_id,file_caseid,file_casename,file_casedetail,file_number,file_name,file_brstatus
		// 	FROM Case_file
		// 	WHERE 1 = 1 ".$condition." ORDER BY file_caseid ASC";

		return $this->db->query($sql);
	}



}
?>